<%@ page language="java" import="org.json.*,java.sql.*,java.util.*,com.microsoft.sqlserver.jdbc.*,java.io.*,web.rec.*" 
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"  %>

<% 
	Connection conn = ResourceTool.getConnection();
%> 
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
<script type="text/javascript" src="script/jquery-1.6.3.min.js"></script>
<script type="text/javascript" src="script/form_script.js"></script>
</head>
<body>
          <%
			try {
			// Create the prepared statement and use it to
			// INSERT the student attrs INTO the Student table.
			Statement stmt = conn.createStatement();
			String action = request.getParameter("action");
			%>
			<table>
			<tr>
				<th>category</th>
				<th>required unit</th>
				<th>units left</th>		
			</tr>
			<%
			String student_id = request.getParameter("student_id");
			String degree_name = request.getParameter("degree_name");
			if (action != null && action.equals("submit"))
			{
				String sql = "select * from Degree, DegreeCategory where "
						   + "Degree.degree_name = DegreeCategory.degree_name and "
						   + "Degree.degree_name = '" + degree_name + "'";
				ResultSet rs = stmt.executeQuery(sql);
				Map<String,Integer> categoryUnits = new HashMap<String, Integer>();
				int catUnitsLeft = 0 , catRequiredUnits, degreeRequiredUnits = 0, degreeUnitsLeft = 0;
				boolean firstRecord = true;
				while(rs.next())
				{
					if(firstRecord)
					{
						degreeRequiredUnits = rs.getInt("unit");
						firstRecord = false;
					}
					categoryUnits.put(rs.getString("category_name"), rs.getInt("required_units"));
				}
				
				for(String category: categoryUnits.keySet())
				{
					sql = "select sum(StudentTakingSection.units) from Student, StudentTakingSection, Section, Class, Course, CourseCategory where "
				   	       + "Student.student_id = StudentTakingSection.student_id and "
						   + "Section.section_id = StudentTakingSection.section_id and "
						   + "Section.class_id = Class.class_id and "
						   + "Class.course_num = Course.course_num and "
						   + "Course.course_num = CourseCategory.course_num and "
						   + "Student.student_id = '"+ student_id + "' and "
						   + "CourseCategory.category_name = '" + category + "'";
					System.out.println(sql);
					rs = stmt.executeQuery(sql);
					catRequiredUnits = categoryUnits.get(category);
					if(rs.next())
					{
						catUnitsLeft = catRequiredUnits - rs.getInt(1);
					}
					%>
					<tr>
					<td><%= category %></td>
					<td><%= catRequiredUnits %></td>
					<td><%= catUnitsLeft %></td>
					</tr>
				<% 
				}
				sql = "select sum(StudentTakingSection.units) from Student, StudentTakingSection, Section where "
				   	       + "Student.student_id = StudentTakingSection.student_id and "
						   + "Section.section_id = StudentTakingSection.section_id and "
						   + "Student.student_id = '"+ student_id + "'";
				System.out.println(sql);
				rs = stmt.executeQuery(sql);
				if(rs.next())
				{
					degreeUnitsLeft = degreeRequiredUnits - rs.getInt(1);
				}
				%>
			<tr>
			<td>units left for this degree: </td>
			<td><%= degreeUnitsLeft %></td>
			</tr>
			<%
		}
	} catch (SQLException sqle) {
		out.println(sqle.getMessage());
	} catch (Exception e) {
		out.println(e.getMessage());
	}    
            %>
      </table>
</body>
</html>